import mysql from 'mysql2/promise';
import { config } from '../config.js';

export class BaseModel {
    constructor() {
        this.pool = mysql.createPool({
            host: config.database.host,
            port: config.database.port,
            user: config.database.user,
            password: config.database.password,
            database: config.database.name,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
    }

    /**
     * 执行SQL查询
     * @param {string} query - SQL查询语句
     * @param {Array} params - 查询参数
     * @returns {Promise<Array>} 查询结果
     */
    async execute(query, params = []) {
        try {
            const [rows] = await this.pool.execute(query, params);
            return rows;
        } catch (error) {
            throw new Error(`Database query failed: ${error.message}`);
        }
    }

    /**
     * 通用查找所有记录（支持分页和搜索）
     * @param {string} tableName - 表名
     * @param {Object} options - 查询选项
     * @returns {Promise<Array>} 查询结果
     */
    async findAll(tableName, options = {}) {
        const {
            select = '*',
            where = '1=1',
            whereParams = [],
            orderBy = 'created_at DESC',
            limit,
            offset
        } = options;

        let query = `SELECT ${select} FROM ${tableName} WHERE ${where}`;
        
        if (orderBy) {
            query += ` ORDER BY ${orderBy}`;
        }
        
        if (limit !== undefined) {
            query += ` LIMIT ?`;
            whereParams.push(limit);
        }
        
        if (offset !== undefined) {
            query += ` OFFSET ?`;
            whereParams.push(offset);
        }

        return await this.execute(query, whereParams);
    }

    /**
     * 通用根据ID查找记录
     * @param {string} tableName - 表名
     * @param {number|string} id - 记录ID
     * @param {string} select - 选择字段
     * @returns {Promise<Object|null>} 查询结果
     */
    async findById(tableName, id, select = '*') {
        const query = `SELECT ${select} FROM ${tableName} WHERE id = ?`;
        const rows = await this.execute(query, [id]);
        return rows.length > 0 ? rows[0] : null;
    }

    /**
     * 通用根据条件查找单条记录
     * @param {string} tableName - 表名
     * @param {Object} conditions - 查询条件
     * @param {string} select - 选择字段
     * @returns {Promise<Object|null>} 查询结果
     */
    async findOne(tableName, conditions, select = '*') {
        const { where, params } = this._buildWhereClause(conditions);
        const query = `SELECT ${select} FROM ${tableName} WHERE ${where} LIMIT 1`;
        const rows = await this.execute(query, params);
        return rows.length > 0 ? rows[0] : null;
    }

    /**
     * 通用统计记录数量
     * @param {string} tableName - 表名
     * @param {Object} conditions - 查询条件
     * @returns {Promise<number>} 记录数量
     */
    async count(tableName, conditions = {}) {
        const { where, params } = this._buildWhereClause(conditions);
        const query = `SELECT COUNT(*) as total FROM ${tableName} WHERE ${where}`;
        const rows = await this.execute(query, params);
        return rows[0].total;
    }

    /**
     * 通用创建记录
     * @param {string} tableName - 表名
     * @param {Object} data - 插入数据
     * @returns {Promise<Object>} 插入结果
     */
    async create(tableName, data) {
        const fields = Object.keys(data);
        const values = Object.values(data);
        const placeholders = fields.map(() => '?').join(', ');
        
        const query = `INSERT INTO ${tableName} (${fields.join(', ')}) VALUES (${placeholders})`;
        const result = await this.execute(query, values);
        
        return {
            insertId: result.insertId,
            affectedRows: result.affectedRows
        };
    }

    /**
     * 通用更新记录
     * @param {string} tableName - 表名
     * @param {number|string} id - 记录ID
     * @param {Object} data - 更新数据
     * @returns {Promise<Object>} 更新结果
     */
    async update(tableName, id, data) {
        const fields = Object.keys(data);
        const values = Object.values(data);
        const setClause = fields.map(field => `${field} = ?`).join(', ');
        
        const query = `UPDATE ${tableName} SET ${setClause} WHERE id = ?`;
        const result = await this.execute(query, [...values, id]);
        
        return {
            affectedRows: result.affectedRows,
            changedRows: result.changedRows
        };
    }

    /**
     * 通用删除记录
     * @param {string} tableName - 表名
     * @param {number|string} id - 记录ID
     * @returns {Promise<Object>} 删除结果
     */
    async delete(tableName, id) {
        const query = `DELETE FROM ${tableName} WHERE id = ?`;
        const result = await this.execute(query, [id]);
        
        return {
            affectedRows: result.affectedRows
        };
    }

    /**
     * 通用软删除记录
     * @param {string} tableName - 表名
     * @param {number|string} id - 记录ID
     * @param {string} statusField - 状态字段名
     * @param {string} deletedValue - 删除状态值
     * @returns {Promise<Object>} 更新结果
     */
    async softDelete(tableName, id, statusField = 'status', deletedValue = 'deleted') {
        return await this.update(tableName, id, {
            [statusField]: deletedValue,
            updated_at: new Date()
        });
    }

    /**
     * 批量更新记录状态
     * @param {string} tableName - 表名
     * @param {Array} ids - 记录ID数组
     * @param {string} status - 新状态
     * @param {string} statusField - 状态字段名
     * @returns {Promise<Object>} 更新结果
     */
    async batchUpdateStatus(tableName, ids, status, statusField = 'status') {
        if (!ids || ids.length === 0) {
            throw new Error('IDs array cannot be empty');
        }
        
        const placeholders = ids.map(() => '?').join(', ');
        const query = `UPDATE ${tableName} SET ${statusField} = ?, updated_at = NOW() WHERE id IN (${placeholders})`;
        const result = await this.execute(query, [status, ...ids]);
        
        return {
            affectedRows: result.affectedRows,
            changedRows: result.changedRows
        };
    }

    /**
     * 构建WHERE子句
     * @param {Object} conditions - 查询条件
     * @returns {Object} WHERE子句和参数
     */
    _buildWhereClause(conditions) {
        if (!conditions || Object.keys(conditions).length === 0) {
            return { where: '1=1', params: [] };
        }
        
        const whereParts = [];
        const params = [];
        
        for (const [key, value] of Object.entries(conditions)) {
            if (value !== undefined && value !== null) {
                whereParts.push(`${key} = ?`);
                params.push(value);
            }
        }
        
        return {
            where: whereParts.length > 0 ? whereParts.join(' AND ') : '1=1',
            params
        };
    }

    /**
     * 开始事务
     * @returns {Promise<Connection>} 数据库连接
     */
    async beginTransaction() {
        const connection = await this.pool.getConnection();
        await connection.beginTransaction();
        return connection;
    }

    /**
     * 提交事务
     * @param {Connection} connection - 数据库连接
     */
    async commitTransaction(connection) {
        await connection.commit();
        connection.release();
    }

    /**
     * 回滚事务
     * @param {Connection} connection - 数据库连接
     */
    async rollbackTransaction(connection) {
        await connection.rollback();
        connection.release();
    }

    /**
     * 关闭数据库连接池
     */
    async close() {
        await this.pool.end();
    }

    /**
     * 获取数据库连接池状态
     * @returns {Object} 连接池状态
     */
    getPoolStatus() {
        return {
            totalConnections: this.pool.pool._allConnections.length,
            freeConnections: this.pool.pool._freeConnections.length,
            acquiringConnections: this.pool.pool._acquiringConnections.length
        };
    }
}